home *** CD-ROM | disk | FTP | other *** search
/ MacWorld 2003 May (DVD) / Macworld Resource DVD May 2003.toast / Data / Software / Bonus / Database / mysql-max-3.23.55.sit / mysql-max-3.23.55-apple-darwi.1 / mysql-test / t / distinct.test < prev    next >
Encoding:
Text File  |  2003-01-21  |  6.0 KB  |  210 lines  |  [TEXT/ttxt]
  1. #
  2. # Bug with distinct and INSERT INTO
  3. # Bug with group by and not used fields
  4. #
  5.  
  6. drop table if exists t1,t2,t3;
  7.  
  8. CREATE TABLE t1 (id int,facility char(20));
  9. CREATE TABLE t2 (facility char(20));
  10. INSERT INTO t1 VALUES (NULL,NULL);
  11. INSERT INTO t1 VALUES (-1,'');
  12. INSERT INTO t1 VALUES (0,'');
  13. INSERT INTO t1 VALUES (1,'/L');
  14. INSERT INTO t1 VALUES (2,'A01');
  15. INSERT INTO t1 VALUES (3,'ANC');
  16. INSERT INTO t1 VALUES (4,'F01');
  17. INSERT INTO t1 VALUES (5,'FBX');
  18. INSERT INTO t1 VALUES (6,'MT');
  19. INSERT INTO t1 VALUES (7,'P');
  20. INSERT INTO t1 VALUES (8,'RV');
  21. INSERT INTO t1 VALUES (9,'SRV');
  22. INSERT INTO t1 VALUES (10,'VMT');
  23. INSERT INTO t2 SELECT DISTINCT FACILITY FROM t1;
  24.  
  25. select id from t1 group by id;
  26. select * from t1 order by id;
  27. select id-5,facility from t1 order by "id-5";
  28. select id,concat(facility) from t1 group by id ;
  29. select id+0 as a,max(id),concat(facility) as b from t1 group by a order by b desc,a;
  30. select id >= 0 and id <= 5 as grp,count(*) from t1 group by grp;
  31.  
  32. SELECT DISTINCT FACILITY FROM t1;
  33. SELECT FACILITY FROM t2;
  34. SELECT count(*) from t1,t2 where t1.facility=t2.facility;
  35. select count(facility) from t1;
  36. select count(*) from t1;
  37. select count(*) from t1 where facility IS NULL;
  38. select count(*) from t1 where facility = NULL;
  39. select count(*) from t1 where facility IS NOT NULL;
  40. select count(*) from t1 where id IS NULL;
  41. select count(*) from t1 where id IS NOT NULL;
  42.  
  43. drop table t1,t2;
  44.  
  45. #
  46. # Problem with distinct without results
  47. #
  48. CREATE TABLE t1 (UserId int(11) DEFAULT '0' NOT NULL);
  49. INSERT INTO t1 VALUES (20);
  50. INSERT INTO t1 VALUES (27);
  51.  
  52. SELECT UserId FROM t1 WHERE Userid=22;
  53. SELECT UserId FROM t1 WHERE UserId=22 group by Userid;
  54. SELECT DISTINCT UserId FROM t1 WHERE UserId=22 group by Userid;
  55. SELECT DISTINCT UserId FROM t1 WHERE UserId=22;
  56. drop table t1;
  57.  
  58. #
  59. # Test of distinct
  60. #
  61.  
  62. CREATE TABLE t1 (a int(10) unsigned not null primary key,b int(10) unsigned);
  63. INSERT INTO t1 VALUES (1,1),(2,1);
  64. CREATE TABLE t2 (a int(10) unsigned not null, key (A));
  65. INSERT INTO t2 VALUES (1),(2);
  66. CREATE TABLE t3 (a int(10) unsigned, key(A), b text);
  67. INSERT INTO t3 VALUES (1,'1'),(2,'2');
  68. SELECT DISTINCT t3.b FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
  69. INSERT INTO t2 values (1),(2),(3);
  70. INSERT INTO t3 VALUES (1,'1'),(2,'2'),(1,'1'),(2,'2');
  71. explain SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
  72. SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
  73.  
  74. # Create a lot of data into t3;
  75. create temporary table t4 select * from t3;
  76. insert into t3 select * from t4;
  77. insert into t4 select * from t3;
  78. insert into t3 select * from t4;
  79. insert into t4 select * from t3;
  80. insert into t3 select * from t4;
  81. insert into t4 select * from t3;
  82. insert into t3 select * from t4;
  83.  
  84. explain select distinct t1.a from t1,t3 where t1.a=t3.a;
  85. #flush status;
  86. select distinct t1.a from t1,t3 where t1.a=t3.a;
  87. #show status like 'Handler%';
  88. #flush status;
  89. select distinct 1 from t1,t3 where t1.a=t3.a;
  90. #show status like 'Handler%';
  91. drop table t1,t2,t3,t4;
  92.  
  93. CREATE TABLE t1 (name varchar(255));
  94. INSERT INTO t1 VALUES ('aa'),('ab'),('ac'),('ad'),('ae');
  95. SELECT DISTINCT * FROM t1 LIMIT 2;
  96. SELECT DISTINCT name FROM t1 LIMIT 2;
  97. SELECT DISTINCT 1 FROM t1 LIMIT 2;
  98. drop table t1;
  99.  
  100. CREATE TABLE t1 (
  101.   ID int(11) NOT NULL auto_increment,
  102.   NAME varchar(75) DEFAULT '' NOT NULL,
  103.   LINK_ID int(11) DEFAULT '0' NOT NULL,
  104.   PRIMARY KEY (ID),
  105.   KEY NAME (NAME),
  106.   KEY LINK_ID (LINK_ID)
  107. );
  108.  
  109. INSERT INTO t1 (ID, NAME, LINK_ID) VALUES (1,'Mike',0),(2,'Jack',0),(3,'Bill',0);
  110.  
  111. CREATE TABLE t2 (
  112.   ID int(11) NOT NULL auto_increment,
  113.   NAME varchar(150) DEFAULT '' NOT NULL,
  114.   PRIMARY KEY (ID),
  115.   KEY NAME (NAME)
  116. );
  117.  
  118. SELECT DISTINCT
  119.     t2.id AS key_link_id,
  120.     t2.name AS link
  121. FROM t1
  122. LEFT JOIN t2 ON t1.link_id=t2.id
  123. GROUP BY t1.id
  124. ORDER BY link;
  125. drop table t1,t2;
  126.  
  127. #
  128. # Problem with table dependencies
  129. #
  130.  
  131. create table t1 (
  132.     id        int not null,
  133.     name    tinytext not null,
  134.     unique    (id)
  135. );
  136. create table t2 (
  137.     id        int not null,
  138.     idx        int not null,
  139.     unique    (id, idx)
  140. );
  141. create table t3 (
  142.     id        int not null,
  143.     idx        int not null,
  144.     unique    (id, idx)
  145. );
  146. insert into t1 values (1,'yes'), (2,'no');
  147. insert into t2 values (1,1);
  148. insert into t3 values (1,1);
  149. EXPLAIN
  150. SELECT DISTINCT
  151.     t1.id
  152. from
  153.     t1
  154.     straight_join
  155.     t2
  156.     straight_join
  157.     t3
  158.     straight_join
  159.     t1 as j_lj_t2 left join t2 as t2_lj
  160.         on j_lj_t2.id=t2_lj.id
  161.     straight_join
  162.     t1 as j_lj_t3 left join t3 as t3_lj
  163.         on j_lj_t3.id=t3_lj.id
  164. WHERE
  165.     ((t1.id=j_lj_t2.id AND t2_lj.id IS NULL) OR (t1.id=t2.id AND t2.idx=2))
  166.     AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2));
  167. SELECT DISTINCT
  168.     t1.id
  169. from
  170.     t1
  171.     straight_join
  172.     t2
  173.     straight_join
  174.     t3
  175.     straight_join
  176.     t1 as j_lj_t2 left join t2 as t2_lj
  177.         on j_lj_t2.id=t2_lj.id
  178.     straight_join
  179.     t1 as j_lj_t3 left join t3 as t3_lj
  180.         on j_lj_t3.id=t3_lj.id
  181. WHERE
  182.     ((t1.id=j_lj_t2.id AND t2_lj.id IS NULL) OR (t1.id=t2.id AND t2.idx=2))
  183.     AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2));
  184. drop table t1,t2,t3;
  185.  
  186. #
  187. # Test using DISTINCT on a function that contains a group function
  188. # This also test the case when one doesn't use all fields in GROUP BY.
  189. #
  190.  
  191. drop table if exists t1;
  192. create table t1 (a int not null, b int not null, t time);
  193. insert into t1 values (1,1,"00:06:15"),(1,2,"00:06:15"),(1,2,"00:30:15"),(1,3,"00:06:15"),(1,3,"00:30:15");
  194. select a,sec_to_time(sum(time_to_sec(t))) from t1 group by a,b;
  195. select distinct a,sec_to_time(sum(time_to_sec(t))) from t1 group by a,b;
  196. create table t2 (a int not null primary key, b int);
  197. insert into t2 values (1,1),(2,2),(3,3);
  198. select t1.a,sec_to_time(sum(time_to_sec(t))) from t1 left join t2 on (t1.b=t2.a) group by t1.a,t2.b;
  199. select distinct t1.a,sec_to_time(sum(time_to_sec(t))) from t1 left join t2 on (t1.b=t2.a) group by t1.a,t2.b;
  200. drop table t1,t2;
  201.  
  202. #
  203. # Test problem with DISTINCT and HAVING
  204. #
  205. create table t1 (a int not null,b char(5), c text);
  206. insert into t1 (a) values (1),(2),(3),(4),(1),(2),(3),(4);
  207. select distinct a from t1 group by b,a having a > 2 order by a desc;
  208. select distinct a,c from t1 group by b,c,a having a > 2 order by a desc;
  209. drop table t1;
  210.